
set define off


prompt
prompt Creating procedure TRYADDTABCOLUMN
prompt ==================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYADDTABCOLUMN  (tabName in varchar2, colName in varchar2, colType in varchar2) is
        n_col int;
        stmt VARCHAR(2000);
        begin
          select count(*) into n_col from cols
                 where table_name = upper(tabName) and column_name = upper(colName);
          if n_col<1 then
            stmt :='alter table '||tabName||' add '||colName||' '||colType;
            execute immediate stmt;
          end if;
        end;
/

prompt
prompt Creating procedure TRYADDTABCOLUMNCOMMIT
prompt ========================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYADDTABCOLUMNCOMMIT  (tabName in varchar2,
                                            colName in varchar2,
                                            commitSQL in varchar2) is
  n_col int;
  stmt  VARCHAR(2000);
begin
  select count(*)
    into n_col
    from cols
   where table_name = upper(tabName)
     and column_name = upper(colName);
  if n_col >= 1 then
    stmt := 'comment on column ' || tabName || '.' || colName || ' is ''' || commitSQL || '''';

    execute immediate stmt;
  end if;
end;
/

prompt
prompt Creating procedure TRYADDTABLE
prompt ==============================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYADDTABLE  (tableName IN varchar2,createTableSql IN varchar2)
authid current_user
is
  row1 number;
begin
  select count(table_name)
    into row1
    from user_tables
   where table_name = upper(tableName);
  IF (row1 <= 0) THEN
    execute immediate createTableSql;
  END IF;
END;
/

prompt
prompt Creating procedure TRYADDTABSEQUENCES
prompt =====================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYADDTABSEQUENCES  (tabName in varchar2, startName in varchar2) is
        n_col int;
        stmt VARCHAR(2000);
        begin
          select count(*) into n_col from user_sequences
                 where sequence_name = upper(tabName);
          if n_col<1 then
            stmt :='create sequence '||tabName||' minvalue 0 maxvalue 9999999999999999999999999999 start with  '||startName||' increment by 1 cache 20';
            execute immediate stmt;
          end if;
        end;
/

prompt
prompt Creating procedure TRYDROPCONSTRAINT
prompt ====================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPCONSTRAINT  (tb_name       VARCHAR2)
authid current_user
IS
   con_count   NUMBER;
   con_name    VARCHAR2 (32);
BEGIN
   con_name := '';
   con_count := 0;

   SELECT COUNT (DISTINCT constraint_name)
     INTO con_count
     FROM user_constraints
    WHERE     table_name = UPPER (tb_name)
          and constraint_type ='P';

   IF con_count > 0
   THEN
      SELECT DISTINCT constraint_name
        INTO con_name
        FROM user_constraints
       WHERE     table_name = UPPER (tb_name)
             and constraint_type ='P';

      EXECUTE IMMEDIATE
         'alter table ' || tb_name || ' drop constraint ' || con_name || ' cascade drop index';
   END IF;
END;
/

prompt
prompt Creating procedure TRYDROPCONSTRAINT_ENGINE
prompt ===========================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPCONSTRAINT_ENGINE  (tb_name       VARCHAR2)
authid current_user
IS
   con_count   NUMBER;
   con_name    VARCHAR2 (32);
BEGIN
   con_name := '';
   con_count := 0;

   SELECT COUNT (DISTINCT constraint_name)
     INTO con_count
     FROM user_constraints
    WHERE     table_name = UPPER (tb_name)
          and constraint_type ='P';

   IF con_count > 0
   THEN
      SELECT DISTINCT constraint_name
        INTO con_name
        FROM user_constraints
       WHERE     table_name = UPPER (tb_name)
             and constraint_type ='P';

      EXECUTE IMMEDIATE
         'alter table ' || tb_name || ' drop constraint ' || con_name || ' cascade drop index';
   END IF;
END;
/

prompt
prompt Creating procedure TRYDROPINDEX
prompt ===============================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPINDEX  (indexName in varchar2) is
      stmt VARCHAR2(2000);
      row1 number;
      begin
      select count(index_name) into row1 from user_indexes where index_name = indexName;
        IF (row1>0) THEN
          stmt :='DROP INDEX '|| indexName;
          execute immediate stmt;
        END IF;
      end;
/

prompt
prompt Creating procedure TRYDROPPRIMARY
prompt =================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPPRIMARY  (tabName in varchar2,primaryIndex in varchar2) is
      stmt VARCHAR2(2000);
      row1 number;
      begin
      select count(index_name) into row1 from user_indexes where index_name = primaryIndex;
        IF (row1>0) THEN
          stmt :='alter table '|| tabName ||' drop constraint   '|| primaryIndex;
          execute immediate stmt;
        END IF;
      end;
/

prompt
prompt Creating procedure TRYDROPSEQUENCE
prompt ==================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPSEQUENCE  (sequenceName IN varchar2) is
  stmt VARCHAR2(2000);
  row1 number;
begin
  select count(sequence_name)
    into row1
    from all_sequences
   where sequence_name = sequenceName;

  IF (row1 > 0) THEN
    stmt := 'DROP SEQUENCE ' || sequenceName;
    execute immediate stmt;
  END IF;
end;
/

prompt
prompt Creating procedure TRYDROPTABCOLUMN
prompt ===================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPTABCOLUMN  (tabName in varchar2, colName in varchar2) is
        n_col int;
        stmt VARCHAR(2000);
        begin
          select count(*) into n_col from cols
                 where table_name = upper(tabName) and column_name = upper(colName);
          if n_col>0 then
            stmt :='alter table '||tabName||' drop column '||colName;
            execute immediate stmt;
          end if;
        end;
/

prompt
prompt Creating procedure TRYDROPTABLE
prompt ===============================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPTABLE  (tableName IN varchar2) is
      stmt VARCHAR2(2000);
      row1 number;
      begin
        select count(table_name) into row1 from user_tables where table_name=upper(tableName);
        IF (row1>0) THEN
          stmt :='DROP TABLE '|| tableName;
          execute immediate stmt;
        END IF;
      END;
/

prompt
prompt Creating procedure TRYDROPVIEW
prompt ==============================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYDROPVIEW  (viewName IN varchar2) is
      stmt VARCHAR2(2000);
      row1 number;
      begin
        select count(view_name) into row1 from user_views where view_name=viewName;
        IF (row1>0) THEN
          stmt :='DROP view '|| viewName;
          execute immediate stmt;
        END IF;
      end;
/

prompt
prompt Creating procedure TRYMODIFYTABCOLUMN
prompt =====================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYMODIFYTABCOLUMN  (tabName in varchar2,
                                            colName in varchar2,
                                            colType in varchar2) is
  n_col int;
  stmt  VARCHAR(2000);
begin
  select count(*)
    into n_col
    from cols
   where table_name = upper(tabName)
     and column_name = upper(colName);
  if n_col = 1 then
    stmt := 'alter table ' || tabName || ' modify ' || colName || ' ' ||
            colType;
    execute immediate stmt;
  end if;
end;
/

prompt
prompt Creating procedure TRYMODIFYTABCOLUMNNOTNULL
prompt ============================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYMODIFYTABCOLUMNNOTNULL  (pTab IN VARCHAR2, pCol IN VARCHAR2) is
begin
  FOR ITEM IN (SELECT COUNT(*) NUM FROM user_tab_columns WHERE TABLE_NAME = UPPER(pTab) AND COLUMN_NAME = UPPER(pCol) AND NULLABLE = 'Y') LOOP
    IF ITEM.NUM = 1 THEN
      EXECUTE IMMEDIATE('ALTER TABLE '|| UPPER(pTab) || ' MODIFY ' || UPPER(pCol) || ' NOT NULL');
    END IF;
  END LOOP;
end TRYMODIFYTABCOLUMNNOTNULL;
/

prompt
prompt Creating procedure TRYMODIFYTABCOLUMNNULL
prompt =========================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYMODIFYTABCOLUMNNULL  (pTab IN VARCHAR2, pCol IN VARCHAR2) is
begin
  FOR ITEM IN (SELECT COUNT(*) NUM FROM user_tab_columns WHERE TABLE_NAME = UPPER(pTab) AND COLUMN_NAME = UPPER(pCol) AND NULLABLE = 'N') LOOP
    IF ITEM.NUM = 1 THEN
      EXECUTE IMMEDIATE('ALTER TABLE '|| UPPER(pTab) || ' MODIFY ' || UPPER(pCol) || ' NULL');
    END IF;
  END LOOP;
end TRYMODIFYTABCOLUMNNULL;
/

prompt
prompt Creating procedure TRYRENAMETABCOLUMN
prompt =====================================
prompt
CREATE OR REPLACE PROCEDURE LZ.TRYRENAMETABCOLUMN  (tabName in varchar2,
                                            oldColName in varchar2,
                                            newColName in varchar2) is
  n_col int;
  stmt  VARCHAR(2000);
begin
  select count(*)
    into n_col
    from cols
   where table_name = upper(tabName)
     and column_name = upper(oldColName);
  if n_col = 1 then
    stmt := 'alter table ' || tabName || ' rename column ' || oldColName || ' to ' ||
            newColName;
    execute immediate stmt;
  end if;
end;
/


prompt Done
spool off
set define on
